﻿namespace AdoNetExecuterLibrary
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Linq;
    using System.Reflection;

    public abstract class BaseExecuter<TConnection, TCommand, TParameter> : IDisposable
        where TConnection : DbConnection
        where TCommand : DbCommand
        where TParameter : DbParameter
    {

        #region Properties
        private TConnection connection;

        public DbTransaction transaction;
        public virtual string ConnectionString
        {
            get
            {
                return this.connection.ConnectionString;
            }
            set
            {
                this.connection.ConnectionString = value;
            }
        }
        public char ParameterStartCharacter { get; set; }
        #endregion

        #region Constructors and Destructor
        public BaseExecuter()
        {
            this.connection = (TConnection)Activator.CreateInstance(typeof(TConnection));
            this.ParameterStartCharacter = '@';
        }
        public BaseExecuter(string connectionString)
            : this()
        {
            this.ConnectionString = connectionString;
        }

        ~BaseExecuter()
        {
            this.Dispose();
        }
        #endregion

        #region Private Methods
        private void executeAction(TCommand command, TParameter[] parameters, Action action)
        {
            if (this.connection.State != ConnectionState.Open)
            {
                this.connection.Open();
            }
            using (command)
            {
                command.Connection = this.connection;
                if (this.transaction != null)
                {
                    command.Transaction = this.transaction;
                }
                if (parameters != null && parameters.Length > 0)
                {
                    foreach (TParameter parameter in parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                }
                action();
            }
        }
        private TCommand createNewCommand(string cmdText)
        {
            var command = (TCommand)Activator.CreateInstance(typeof(TCommand));
            command.CommandText = cmdText;
            return command;
        }
        private TCommand createNewCommand(string formatSql, object[] values)
        {
            TParameter[] parameters = new TParameter[values.Length];
            for (int i = 0; i < values.Length; i++)
            {
                string parameterName = string.Concat(this.ParameterStartCharacter, "p", i);
                formatSql = formatSql.Replace(string.Concat("{", i, "}"), parameterName);
                TParameter parameter = (TParameter)Activator.CreateInstance(typeof(TParameter));
                parameter.ParameterName = parameterName;
                parameter.Value = values[i];
                parameters[i] = parameter;
            }
            TCommand result = this.createNewCommand(formatSql);
            if (parameters.Length > 0)
            {
                result.Parameters.AddRange(parameters);
            }
            return result;
        }

        #endregion

        #region ExecuteNonQuery
        public int ExecuteNonQuery(TCommand command, params TParameter[] parameters)
        {
            int result = -1;
            this.executeAction(command, parameters, () =>
            {
                result = command.ExecuteNonQuery();
            });
            return result;
        }
        public int ExecuteNonQuery(string sql, params TParameter[] parameters)
        {
            return this.ExecuteNonQuery(this.createNewCommand(sql), parameters);
        }
        public int ExecuteNonQuery(string formatSql, params object[] values)
        {
            TCommand command = this.createNewCommand(formatSql, values);
            return this.ExecuteNonQuery(command: command, parameters: null);
        }

        #endregion

        #region Sql Query
        public DataTable SqlQuery(TCommand command, params TParameter[] parameters)
        {
            DataTable table = new DataTable();
            this.executeAction(command, parameters, () =>
            {
                table.Load(command.ExecuteReader());
            });
            return table;
        }
        public DataTable SqlQuery(string sql, params TParameter[] parameters)
        {
            return this.SqlQuery(this.createNewCommand(sql), parameters);
        }
        public DataTable SqlQuery(string formatSql, params object[] values)
        {
            TCommand command = this.createNewCommand(formatSql, values);
            return this.SqlQuery(command: command, parameters: null);
        }
        public List<T> SqlQuery<T>(TCommand command, params TParameter[] parameters) where T : class
        {
            List<T> list = new List<T>();
            using (var table = this.SqlQuery(command, parameters))
            {
                var props = typeof(T).GetProperties();
                var columns = table.Columns.Cast<DataColumn>().Select(s => s.ColumnName).ToArray();
                if (columns.Any(a => props.Select(s => s.Name).Contains(a)))
                {
                    foreach (DataRow row in table.Rows)
                    {
                        T item = (T)Activator.CreateInstance(typeof(T));
                        var type = item.GetType();
                        props = type.GetProperties();
                        foreach (PropertyInfo prop in props)
                        {
                            if (columns.Any(a => a == prop.Name))
                            {
                                object value = row[prop.Name];
                                if (value is System.DBNull)
                                {
                                    value = null;
                                }
                                prop.SetValue(item, value, null);
                            }
                        }
                        list.Add(item);
                    }
                }
            }
            list.TrimExcess();
            return list;
        }
        public List<T> SqlQuery<T>(string sql, params TParameter[] parameters) where T : class
        {
            return this.SqlQuery<T>(this.createNewCommand(sql), parameters);
        }
        public List<T> SqlQuery<T>(string formatSql, params object[] values) where T : class
        {
            TCommand command = this.createNewCommand(formatSql, values);
            return this.SqlQuery<T>(command: command, parameters: null);
        }
        #endregion

        #region Execute Scalar
        public object ExecuteScalar(TCommand command, params TParameter[] parameters)
        {
            object result = null;
            this.executeAction(command, parameters, () =>
            {
                result = command.ExecuteScalar();

            });
            return result;
        }
        public object ExecuteScalar(string sql, params TParameter[] parameters)
        {
            return this.ExecuteScalar(this.createNewCommand(sql), parameters);
        }
        public object ExecuteScalar(string formatSql, params object[] values)
        {
            TCommand command = this.createNewCommand(formatSql, values);
            return this.ExecuteScalar(command: command, parameters: null);
        }

        #endregion


        public void BeginTransaction(Action operationsInTransaction)
        {
            if (this.connection.State != ConnectionState.Open)
            {
                this.connection.Open();
            }
            this.transaction = this.connection.BeginTransaction();
            try
            {
                operationsInTransaction();
                this.transaction.Commit();
            }
            catch
            {
                this.transaction.Rollback();
                throw;
            }

        }

        #region IDisposable Method
        public void Dispose()
        {
            if (this.transaction != null)
            {
                this.transaction.Dispose();
                this.transaction = null;
            }
            if (this.connection != null)
            {
                if (this.connection.State != ConnectionState.Closed)
                {
                    this.connection.Close();
                }
                this.connection.Dispose();
                this.connection = null;
            }
            GC.SuppressFinalize(this);
        }
        #endregion
    }
}
